package com.wendyxie.sproutup.db

import android.content.ContentValues
import android.content.Context
import android.database.Cursor
import android.database.sqlite.SQLiteDatabase
import android.util.Log
import com.wendyxie.sproutup.entity.UserScoreRecordEntity
import com.wendyxie.sproutup.enum.ScoreStatusEnum
import com.wendyxie.sproutup.utils.RandomUtil
import java.time.LocalDateTime
import java.time.format.DateTimeFormatter


class SQLUserScoreRecordHelper {

    // 定义静态变量
    companion object {
        private const val TABLE_NAME = "user_score_record"
        private const val KEY_ID = "id"
        private const val KEY_USER_ID = "user_id"
        private const val KEY_BABY_ID = "baby_id"
        private const val KEY_SCORE = "score"
        private const val KEY_TOTAL_SCORE_START = "total_score_start"
        private const val KEY_TOTAL_SCORE_END = "total_score_end"
        private const val KEY_SCORE_TITLE = "score_title"
        private const val KEY_SCORE_TMPL_ID = "score_tmpl_id"
        private const val KEY_SCORE_STATUS = "score_status"
        private const val KEY_SCORE_TIME = "score_time"

        /**
         * 创建表
         */
        fun createUserScoreRecordTable(db: SQLiteDatabase) {
            // 创建表： 用户得分明细
            val createScoreRecordTable = "CREATE TABLE user_score_record (" +
                    "id varchar(36) not null PRIMARY KEY, " +
                    "user_id varchar(36) default '' , " + // 用户id
                    "baby_id varchar(36) default '', " + // 宝宝编号
                    "score int," + // 用户得分
                    "total_score_start int, " + // 总得分（不包含本次得分）
                    "total_score_end int, " + // 总得分（包含本次得分）
                    "score_title varchar(100), " + // 得分原因
                    "score_tmpl_id varchar(36), " +
                    "score_status varchar(36), " + // 得分状态：CREATE-创建，COLLECT-已归集，DELETE-已删除
                    "score_time datetime DEFAULT CURRENT_TIMESTAMP )"
            db.execSQL(createScoreRecordTable)
        }


        // 获取总分数
        fun addUserScoreRecord(userScoreRecordEntity: UserScoreRecordEntity, db: SQLiteDatabase) {
            // 创建 ContentValues 对象，用于存储插入的数据
            val contentValues = ContentValues()
            contentValues.put(KEY_ID, RandomUtil.getUUID())
            contentValues.put(KEY_USER_ID, userScoreRecordEntity.userId)
            contentValues.put(KEY_BABY_ID, userScoreRecordEntity.babyId)
            contentValues.put(KEY_SCORE, userScoreRecordEntity.score)
            // 新增时，总分需要需要从数据库中获取
            contentValues.put(KEY_TOTAL_SCORE_START, userScoreRecordEntity.totalScoreStart)
            contentValues.put(KEY_TOTAL_SCORE_END, userScoreRecordEntity.totalScoreEnd)
            contentValues.put(KEY_SCORE_TITLE, userScoreRecordEntity.scoreTitle)
            contentValues.put(KEY_SCORE_TMPL_ID, userScoreRecordEntity.scoreTmplId)
            if (userScoreRecordEntity.scoreStatus == null) {
                contentValues.put(KEY_SCORE_STATUS, ScoreStatusEnum.CREATE.value)
            } else {
                contentValues.put(KEY_SCORE_STATUS, userScoreRecordEntity.scoreStatus!!.value)
            }
            // 设置为 当前时间
            contentValues.put(
                KEY_SCORE_TIME,
                LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"))
            )
            Log.d("addUserScoreRecord", "contentValues: $contentValues")
            db.insertOrThrow(TABLE_NAME, null, contentValues)
        }

        // 收集：单个
        fun collectUserScoreRecord(userScoreRecordEntity: UserScoreRecordEntity, db: SQLiteDatabase) {
            val contentValues = ContentValues()
            contentValues.put(KEY_SCORE_STATUS, ScoreStatusEnum.COLLECT.value)
            db.update(
                TABLE_NAME,
                contentValues,
                "$KEY_ID = ?",
                arrayOf(userScoreRecordEntity.id)
            )
        }

        // 收集：所有待收集
        fun collectUserScoreRecordAll(userScoreRecordEntity: UserScoreRecordEntity, db: SQLiteDatabase) {
            val contentValues = ContentValues()
            contentValues.put(KEY_SCORE_STATUS, ScoreStatusEnum.COLLECT.value)
            db.update(
                TABLE_NAME,
                contentValues,
                "$KEY_SCORE_STATUS = '${ScoreStatusEnum.CREATE.value}' and baby_id = ?",
                arrayOf(userScoreRecordEntity.babyId)
            )
        }

        // 根据 scoreStatus 查询记录
        fun queryRecordListByScoreStatus(queryParam: UserScoreRecordEntity, db: SQLiteDatabase): List<UserScoreRecordEntity> {
            val sql =
                "SELECT * FROM $TABLE_NAME where $KEY_SCORE_STATUS = ? order by $KEY_SCORE_TIME desc, $KEY_TOTAL_SCORE_END desc limit ? offset ?"
            val offset = queryParam.pageSize * (queryParam.page - 1)
            val params = arrayOf(queryParam.scoreStatus!!.value, queryParam.pageSize.toString(), offset.toString())
            val cursor = db.rawQuery(sql, params)
            val scoreList = mutableListOf<UserScoreRecordEntity>()
            while (cursor.moveToNext()) {
                scoreList.add(cursorToUserScoreRecordEntity(cursor, queryParam.pageSize, queryParam.page))
            }
            cursor.close()
            Log.d("queryRecordListByScoreStatus", "scoreList: $scoreList")
            return scoreList
        }

        // 根据 scoreStatus 查询记录
        fun queryRecordList(queryParam: UserScoreRecordEntity, db: SQLiteDatabase): List<UserScoreRecordEntity> {
            val offset = queryParam.pageSize * (queryParam.page - 1)
            val params = arrayOf(queryParam.pageSize.toString(), offset.toString())
            Log.d("queryRecordList", "params: $params")
            val cursor = db.query(
                TABLE_NAME,
                null,
                null,
                null,
                null,
                null,
                "$KEY_SCORE_TIME desc, $KEY_TOTAL_SCORE_END desc",
                " ${queryParam.pageSize} offset $offset"
            )
            val scoreList = mutableListOf<UserScoreRecordEntity>()
            while (cursor.moveToNext()) {
                scoreList.add(cursorToUserScoreRecordEntity(cursor, queryParam.pageSize, queryParam.page))
            }
            Log.d("queryRecordList", "scoreList: $scoreList")
            cursor.close()
            return scoreList
        }

        /**
         * 查询：根据tmplId如何查看
         */
        fun queryRecordListByTmplId(tmplId: String, db: SQLiteDatabase): UserScoreRecordEntity? {
            val sql = "SELECT * FROM $TABLE_NAME where $KEY_SCORE_TMPL_ID = ? order by $KEY_SCORE_TIME desc limit 1"
            val params = arrayOf(tmplId)
            val cursor = db.rawQuery(sql, params)
            val scoreList = mutableListOf<UserScoreRecordEntity>()
            while (cursor.moveToNext()) {
                scoreList.add(cursorToUserScoreRecordEntity(cursor, 0, 0))
            }
            cursor.close()
            return scoreList.getOrNull(0)
        }


        private fun cursorToUserScoreRecordEntity(
            cursor: Cursor,
            pageSize: Int = 10,
            page: Int = 1
        ): UserScoreRecordEntity {
            return UserScoreRecordEntity(
                cursor.getString(cursor.getColumnIndex(KEY_ID)),
                cursor.getString(cursor.getColumnIndex(KEY_USER_ID)),
                cursor.getString(cursor.getColumnIndex(KEY_BABY_ID)),
                cursor.getInt(cursor.getColumnIndex(KEY_SCORE)),
                cursor.getInt(cursor.getColumnIndex(KEY_TOTAL_SCORE_START)),
                cursor.getInt(cursor.getColumnIndex(KEY_TOTAL_SCORE_END)),
                cursor.getString(cursor.getColumnIndex(KEY_SCORE_TITLE)),
                cursor.getString(cursor.getColumnIndex(KEY_SCORE_TMPL_ID)),
                ScoreStatusEnum.fromString(cursor.getString(cursor.getColumnIndex(KEY_SCORE_STATUS))),
                LocalDateTime.parse(
                    cursor.getString(cursor.getColumnIndex(KEY_SCORE_TIME)),
                    DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")
                ),
                pageSize,
                page
            )
        }
    }


}
